<?php
/**
 * 核销员相关方法类
 * Created by PhpStorm.
 * User: Mli
 * Date: 2018/12/1
 * Time: 18:49
 */

namespace Api\Controller;

use Think\Controller;
use Think\Log;

class CouponBusinessToolsController extends Controller
{

    function __construct()
    {
        parent::__construct();
        ini_set("ignore_user_abort", true);
        ignore_user_abort(true);
        ini_set("max_execution_time", 0);
        set_time_limit(0);
    }

    /**
     * 定时获取商家的统计数据，优惠券用
     */
    public function getBusinessData()
    {
        $preDb = I('pre', '');
        if (empty($preDb)) {
            $this->returnok('', 'illegal request');
        }
        $this->preDb = $preDb;
        $this->businessModel = M('business_info bi', $preDb);
        $this->employeeModel = M('business_employee', $preDb);
        $this->couponModel = M('coupon', $preDb);
        $this->customerModel = M('business_customer', $preDb);
        $this->browseLogModel = M('user_browse_log bl', $preDb);
        $this->receiveModel = M('receive r', $preDb);
        $this->shareModel = M('user_share_log sl', $preDb);
        $this->businessDataModel = M('business_data', $preDb);
        $this->businessCustomerModel = M('business_customer', $preDb);

        $this->_getBusinessData(0);
    }

    private function _getBusinessData($page)
    {
        $preDb = $this->preDb;
        $limit = 30;
        $businessList = $this->businessModel
            ->join('left join ' . $preDb . 'wx_user as wu on bi.user_id = wu.id')
            ->field('bi.id as business_id,bi.user_id,bi.business_name,bi.create_time as register_time,bi.status,wu.name as username,wu.login_times,wu.login_days,wu.last_login_time,wu.unionid,wu.create_time first_login_time,bi.is_template,wu.scene')
            ->limit(($page * $limit), $limit)
            ->order('bi.id asc')
            ->select();


        foreach ($businessList as $k => $v) {
            $businessId = $v['business_id'];
            //店员数量
            $employeeNum = $this->employeeModel->where(array('business_id' => $businessId, 'status' => 1))->count();
            //发布活动数，测试活动数
            $couponInfo = $this->couponModel
                ->field('count(*) as activity_num,count(if(is_test=1,1,null)) as test_activity_num')
                ->where(array('business_id' => $businessId, 'is_on_using' => 2, 'status' => 1))
                ->select();
            //顾客数,截止昨天的顾客数
            $customerInfo = $this->customerModel
                ->where(array('business_id' => $businessId, 'status' => 1))
                ->field('count(*) as customer_num,
               count(IF (create_time < DATE_FORMAT(NOW(),\'%Y-%m-%d 00:00:00\'),1,NULL)) AS yesterday_customer_num')
                ->select();
            //有浏览量的活动数，浏览人数，3天内的浏览人数，截止昨天的浏览人数，浏览次数，截止昨天的浏览次数
            $browseActivity = $this->browseLogModel
                ->join('left join ' . $preDb . 'coupon cc on cc.id = bl.coupon_id')
                ->where('cc.business_id =' . $businessId . ' AND cc.`status`=1 AND cc.is_on_using =2')
                ->field('count(DISTINCT bl.coupon_id) AS browse_activity_num,
	               count(DISTINCT bl.user_id) AS browse_person_num,
	               count(DISTINCT bl.user_id,IF(bl.create_time < DATE_FORMAT(NOW(),\'%Y-%m-%d 00:00:00\'),1,NULL)) AS yesterday_browse_person_num,
	               count(DISTINCT bl.user_id,IF(bl.create_time >= DATE_FORMAT(NOW() - INTERVAL 3 DAY,\'%Y-%m-%d %H:%i:%s\'),1,NULL)) AS three_days_browse_num,
	               count(*) AS browse_num,
	               count(IF(bl.create_time >= bl.create_time < DATE_FORMAT(NOW(),\'%Y-%m-%d 00:00:00\'),1,NULL)) AS yesterday_browse_num')
                ->select();

            //最后操作时间,核销人数，3天内核销人数,截止昨天的核销人数
            $receiveInfo = $this->receiveModel
                ->join('left join ' . $preDb . 'coupon cc on cc.id = r.coupon_id')
                ->where('cc.business_id =' . $businessId . ' AND cc.`status`=1 AND cc.is_on_using =2')
                ->field('max(verify_time) as last_operate_time,count(DISTINCT r.user_id,IF(r.is_use=1,1,null)) as verify_person_num,
                    count(DISTINCT r.user_id,IF(r.is_use=1 AND r.verify_time>= DATE_FORMAT(NOW()- interval 3 day,\'%Y-%m-%d %H:%i:%s\'),1,null)) as three_days_verify_num,
                    count(DISTINCT r.user_id,IF(r.is_use=1 AND r.verify_time < DATE_FORMAT(NOW(),\'%Y-%m-%d 00:00:00\'),1,null)) as yesterday_verify_person_num,
                    count(IF(r.is_use=1,1,null)) as verify_num,
                    count(IF(r.is_use=1 AND r.verify_time < DATE_FORMAT(NOW(),\'%Y-%m-%d 00:00:00\'),1,null)) as yesterday_verify_num,
                    count(IF(r.is_gift =0,1,NULL)) as receive_num,
                    count(IF(r.is_use=1 AND r.verify_type =2,1,NULL)) as scan_verify_num,
                    count(IF(r.is_use=1 AND r.verify_type =1,1,NULL)) as input_verify_num')
                ->order('r.verify_time DESC')
                ->select();

            $shareInfo = $this->shareModel
                ->join('left join ' . $preDb . 'coupon as cc on cc.id = sl.coupon_id')
                ->where('cc.business_id =' . $businessId . ' AND cc.`status`=1 AND cc.is_on_using =2 AND sl.status =1')
                ->field('count(DISTINCT sl.user_id) as share_num')
                ->select();
//        商家给客户添加过备注数量
            $noteInfo = $this->businessCustomerModel->where(array('remarks' => array('NEQ', ''), 'business_id' => $businessId, 'status' => 1))->count();
            $v['activity_num'] = $couponInfo[0]['activity_num'];
            $v['browse_activity_num'] = $browseActivity[0]['browse_activity_num'];
            $v['test_activity_num'] = $couponInfo[0]['test_activity_num'];
            $v['last_operate_time'] = $receiveInfo[0]['last_operate_time'];
            $v['browse_person_num'] = $browseActivity[0]['browse_person_num'];
            $v['yesterday_browse_person_num'] = $browseActivity[0]['yesterday_browse_person_num'];
            $v['browse_num'] = $browseActivity[0]['browse_num'];
            $v['yesterday_browse_num'] = $browseActivity[0]['yesterday_browse_num'];
            $v['customer_num'] = $customerInfo[0]['customer_num'];
            $v['yesterday_customer_num'] = $customerInfo[0]['yesterday_customer_num'];
            $v['verify_person_num'] = $receiveInfo[0]['verify_person_num'];
            $v['yesterday_verify_person_num'] = $receiveInfo[0]['yesterday_verify_person_num'];
            $v['verify_num'] = $receiveInfo[0]['verify_num'];
            $v['yesterday_verify_num'] = $receiveInfo[0]['yesterday_verify_num'];
            $v['receive_num'] = $receiveInfo[0]['receive_num'];
            $v['share_num'] = $shareInfo[0]['share_num'];
            $v['employee_num'] = $employeeNum;
            $v['three_days_browse_num'] = $browseActivity[0]['three_days_browse_num'];
            $v['three_days_verify_num'] = $receiveInfo[0]['three_days_verify_num'];
            $v['scan_verify_num'] = $receiveInfo[0]['scan_verify_num'];//扫码核销次数
            $v['input_verify_num'] = $receiveInfo[0]['input_verify_num'];//手动核销次数
            $v['note_num'] = $noteInfo ? $noteInfo : 0;
            $v['is_template'] = $v['is_template'] ? $v['is_template'] : 0;
            $count = $this->businessDataModel->where(array('business_id' => $businessId))->count();
            if ($count > 0) {
                $this->businessDataModel->where(array('business_id' => $businessId))->save($v);
            } else {
                $this->businessDataModel->add($v);
            }
        }
        if (sizeof($businessList) >= $limit) {
            //递归
            $this->_getBusinessData($page + 1);
        } else {
            Log::write(date('Y-m-d H:i:s') . '执行成功了优惠券getBusinessData');
            $this->returnok('', 'getBusinessData--end');
        }
    }

    /**
     * 获取每天的商家数据，优惠券用
     */
    public function getBusinessDataDaily(){
        $preDb = I('pre', '');
        if (empty($preDb)) {
            $this->returnok('', 'illegal request');
        }
        $this->preDb = $preDb;
        $this->businessModel = M('business_info bi', $preDb);
        $this->couponModel = M('coupon', $preDb);
        $this->customerModel = M('business_customer', $preDb);
        $this->browseLogModel = M('user_browse_log bl', $preDb);
        $this->receiveModel = M('receive r', $preDb);
        $this->shareModel = M('user_share_log sl', $preDb);
        $this->businessDailyModel = M('business_daily_data', $preDb);

        $this->_getBusinessDataDaily(0);
    }
    private function _getBusinessDataDaily($page)
    {
        $date = I('date');
        if (empty($date)) {
            $date = date('Y-m-d', time() - 86400);
        }
        $date1 = date('Y-m-d', strtotime('+1 day', strtotime($date)));
        $limit = 30;
        $preDb = $this->preDb;
        $businessList = $this->businessModel
            ->field('bi.id as business_id,bi.user_id,bi.status,bi.create_time')
            ->where("bi.create_time < '$date1'")
            ->limit(($page * $limit), $limit)
            ->order('bi.id asc')
            ->select();

        $data = array();
        foreach ($businessList as $k => $v) {
            $businessId = $v['business_id'];
            $activityNum = $this->couponModel->where(array(
                'business_id' => $businessId,
                'status' => 1,
                'is_on_using' => 2,
                'release_time' => array(
                    array('elt', $date . ' 23:59:59'), array('egt', $date . ''), 'and'
                )
            ))->count();

            $couponInfo = $this->browseLogModel
                ->where('bl.business_id =' . $businessId . ' AND bl.create_time <= "' . $date . ' 23:59:59" AND bl.create_time >= "' . $date . '"')
                ->field('
	               count(DISTINCT bl.user_id) AS browse_person_num,
	               count(*) AS browse_num')
                ->select();

            $receiveNum = $this->receiveModel //昨天的领取人数
                ->where('r.business_id =' . $businessId . ' AND r.create_time <= "' . $date . ' 23:59:59" AND r.create_time >= "' . $date . '" AND r.is_gift = 0')
                ->count();
            $verifyNum = $this->receiveModel //昨天的核销人数
                ->where('r.business_id =' . $businessId . ' AND r.verify_time <= "' . $date . ' 23:59:59" AND r.verify_time >= "' . $date . '" ')
                ->count();
            $shareInfo = $this->shareModel
                ->join('left join ' . $preDb . 'coupon as cc on cc.id = sl.coupon_id')
                ->where('cc.business_id =' . $businessId . ' AND sl.status =1
                AND sl.create_time <= "' . $date . ' 23:59:59" AND sl.create_time >= "' . $date . '"')
                ->field('count(DISTINCT sl.user_id) as share_num')
                ->select();

            $customerNum = $this->customerModel
                ->where(array(
                    'business_id' => $businessId,
                    'status' => 1,
                    'create_time' => array(
                        array('elt', $date . ' 23:59:59'), array('egt', $date . ''), 'and')
                ))
                ->count();
            $v['date'] = $date;
            $v['activity_num'] = $activityNum;
            $v['browse_person_num'] = $couponInfo[0]['browse_person_num'];
            $v['browse_num'] = $couponInfo[0]['browse_num'];
            $v['receive_num'] = $receiveNum;
            $v['verify_num'] = $verifyNum;
            $v['share_num'] = $shareInfo[0]['share_num'];
            $v['customer_num'] = $customerNum;

            $count = $this->businessDailyModel->where(array('business_id' => $businessId, 'date' => $date))->count();
            if ($count > 0) {
                $this->businessDailyModel->where(array('business_id' => $businessId, 'date' => $date))->save($v);
            } else {
                $data[] = $v;
            }
        }
        $this->businessDailyModel->addAll($data);
        if (sizeof($businessList) >= $limit) {
            //递归
            sleep(1);
            $this->_getBusinessDataDaily($page + 1);
        } else {
            Log::write('定时任务获取每天的商家数据--执行成功');
            $this->returnok('', 'getBusinessDataDaily--end');
        }
    }

    /**
     * 每天生成用户的统计数据，优惠券用
     */
    public function createCustomerData(){
        $preDb = I('pre', '');
        if (empty($preDb)) {
            $this->returnok('', 'illegal request');
        }
        $this->preDb = $preDb;
        $this->customerModel = M("business_customer", $preDb);
        $this->receiveModel = M("receive", $preDb);
        $this->browseModel = M("user_browse_log", $preDb);
        $this->businessCustomerdataModel = M("business_customer_data", $preDb);
        $this->_createCustomerData(0);
    }
    private function _createCustomerData($page)
    {
        $limit = 50;
        $customers = $this->customerModel
            ->field("id,user_id,business_id")
            ->where("status = 1")
            ->order("id asc")
            ->limit(($page * $limit), $limit)
            ->select();

        foreach ($customers as $key => $customer) {
            $id = $customer['id'];
            $user_id = $customer['user_id'];
            $business_id = $customer['business_id'];
            //查询领取数量
            $receive_num = $this->receiveModel->where("user_id = $user_id and business_id = $business_id")->count();
            //查询核销次数
            $verify_num = $this->receiveModel->where("user_id = $user_id and business_id = $business_id and is_use = 1")->count();
            //浏览次数
            $browse_num = $this->browseModel->where("user_id = $user_id and business_id = $business_id")->count();
            //浏览天数
            $sql = "SELECT  COUNT(DISTINCT DATE_FORMAT(create_time,'%y-%m-%d')) as num from " . $this->preDb . "user_browse_log WHERE business_id = $business_id and user_id = $user_id;";
            $browse_days = $this->browseModel->query($sql);
            $count = $this->businessCustomerdataModel->where(array('customer_id' => $id))->count();
            $data = array(
                'business_id' => $business_id,
                'user_id' => $user_id,
                'customer_id' => $id,
                'receive_num' => $receive_num,
                'verify_num' => $verify_num,
                'browse_num' => $browse_num,
                'browse_days' => $browse_days[0]['num'],
            );
            if ($count > 0) {
                $this->businessCustomerdataModel->where(array('customer_id' => $id))->save($data);
            } else {
                $this->businessCustomerdataModel->add($data);
            }
        }
        if (sizeof($customers) >= $limit) {
            //递归
            sleep(1);
            $this->_createCustomerData($page + 1);
        } else {
            Log::write('定时任务获取每天的商家数据--执行成功');
            $this->returnok('', 'getBusinessDataDaily--end');
        }
    }
}